﻿Imports System.Text.RegularExpressions
Imports System.Data.OleDb
Imports System.Data.SqlClient
Public Class frmGestionEntreprises
    Dim dbEntreprises As New cls_taco
    Dim saisie As Boolean
    Dim idEntreprise As Integer
    Dim messageErreur As String

    Sub remplirListView()
        btnSupprimer.Enabled = False
        btnModifier.Enabled = False
        'créé la connection à la base de données
        dbEntreprises = New cls_taco
        dbEntreprises.defDataset(creerRequete(), "tblEntreprises")

        lstResultat.Clear()

        'créé une colonne dans la listView pour chaque élément de la requête SQL
        For Each colonne As DataColumn In dbEntreprises.ds.Tables("tblEntreprises").Columns
            If colonne.ColumnName <> "noEntreprise" Then
                lstResultat.Columns.Add(colonne.ColumnName, 130)
            End If
        Next
        'créé un élément dans le listView pour chaque enregistrement retourné par la requête SQL
        For Each client As DataRow In dbEntreprises.ds.Tables("tblEntreprises").Rows

            Dim LI As New ListViewItem
            LI.Text = client.Item(0)

            For ctr As Integer = 1 To dbEntreprises.ds.Tables("tblEntreprises").Columns.Count - 1
                LI.SubItems.Add(nvl(client.Item(ctr), ""))
            Next

            lstResultat.Items.Add(LI)

        Next

    End Sub


    Public Function creerRequete() As OleDbCommand

        Dim requete As String = "SELECT nom AS Nom, telephone as Telephone, adresse As Adresse, ville, actif as Actif, noEntreprise FROM tblEntreprises "
        Dim maCommande As New OleDbCommand

        If cboProvince.Text <> "Tous" And cboProvince.Text <> "" Then
            requete = requete & IIf(requete.Contains("WHERE"), " AND", "WHERE") & " CodeProvince = (select CodeProvince from tblProvinces where nomProvince=@province)"
            maCommande.Parameters.Add("@province", OleDbType.BSTR, 50).Value = cboProvince.Text
        End If

        If cboVille.Text <> "Tous" And cboVille.Text <> "" Then
            ' requete = requete & IIf(requete.Contains("WHERE"), " AND", "WHERE") & " ville='Trois-Rivières'"
            requete = requete & IIf(requete.Contains("WHERE"), " AND", "WHERE") & " ville = @ville"
            maCommande.Parameters.Add("@ville", OleDbType.BSTR, 50).Value = cboVille.Text
        End If

        If txtCodePostal.Text <> "" Then
            ' requete = requete & IIf(requete.Contains("WHERE"), " AND", "WHERE") & " ville='Trois-Rivières'"
            requete = requete & IIf(requete.Contains("WHERE"), " AND", "WHERE") & " codepostal=@codepostal"
            maCommande.Parameters.Add("@codepostal", OleDbType.BSTR, 50).Value = txtCodePostal.Text
        End If

        If txtTel.Text <> "" Then
            requete = requete & IIf(requete.Contains("WHERE"), " AND", "WHERE") & " telephone=@telephone"
            maCommande.Parameters.Add("@telephone", OleDbType.BSTR, 50).Value = txtTel.Text
        End If
       

        If txtNomEntreprise.Text <> "" Then
            requete = requete & IIf(requete.Contains("WHERE"), " AND", "WHERE") & " nom LIKE @nom"
            maCommande.Parameters.Add("@nom", OleDbType.BSTR, 50).Value = "%" & txtNomEntreprise.Text & "%"
        End If
        If txtAdresse.Text <> "" Then
            requete = requete & IIf(requete.Contains("WHERE"), " AND", "WHERE") & " adresse=@adresse"
            maCommande.Parameters.Add("@adresse", OleDbType.BSTR, 50).Value = txtAdresse.Text
        End If

        If chkActif.Checked = True Then
            requete = requete & IIf(requete.Contains("WHERE"), " AND", "WHERE") & " actif=true"

        End If

        maCommande.CommandText = requete

        Return maCommande

    End Function

    Public Overloads Function ShowDialog(ByVal v_saisie As Boolean) As Windows.Forms.DialogResult

        If v_saisie = True Then
            saisie = True
            btnSelectionner.Visible = True
            btnSelectionner.Enabled = False
        Else
            saisie = False
            btnSelectionner.Visible = False
        End If


        Return MyBase.ShowDialog()
    End Function
    Public Function validerDonnee()
        Return validerTel(txtTel.Text, messageErreur, True) = True And validerNomAutre(txtNomEntreprise.Text, messageErreur, True) _
       And validerAdresse(txtAdresse.Text, messageErreur, True) And validerCodePostal(txtCodePostal.Text, messageErreur, True)
    End Function

    Private Sub frmGestionEntreprises_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        btnSupprimer.Enabled = False
        btnModifier.Enabled = False
        btnRechercheDetaillee.PerformClick()
        btnRechercher.PerformClick()
        remplirListCbo("province", "Tous", cboProvince, "")
        remplirListCbo("ville", "Tous", cboVille, " WHERE v.CodeProvince = p.CodeProvince AND p.nomProvince = '" & cboProvince.SelectedItem & "'")

    End Sub

    Private Sub btnSupprimer_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSupprimer.Click
        Dim etatFinal As Boolean

        etatFinal = IIf(btnSupprimer.Text = "Supprimer", False, True)

        For Each entreprise As DataRow In dbEntreprises.ds.Tables("tblEntreprises").Rows

            If entreprise.Item(0) = lstResultat.SelectedItems(0).Text Then

                entreprise.Item("actif") = etatFinal

                Exit For

            End If

        Next

        dbEntreprises.update("tblEntreprises")

        remplirListView()
    End Sub

    Private Sub btnModifier_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnModifier.Click
        idEntreprise = getNoEntreprise(lstResultat.SelectedItems(0).SubItems(0).Text, lstResultat.SelectedItems(0).SubItems(2).Text)

        inscriptionEntreprise.ShowDialog(idEntreprise)

    End Sub

    Private Sub btnAjouter_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAjouter.Click

        inscriptionEntreprise.ShowDialog(Nothing)
    End Sub

    Private Sub btnRechercheDetaillee_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnRechercheDetaillee.Click
        If panRecherche.Visible = True Then
            panRecherche.Visible = False
            lstResultat.Height += panRecherche.Height
            lstResultat.Top -= panRecherche.Height
            btnRechercheDetaillee.Text = "/\"
        Else
            panRecherche.Visible = True
            lstResultat.Height -= panRecherche.Height
            lstResultat.Top += panRecherche.Height
            btnRechercheDetaillee.Text = "\/"
        End If
    End Sub

    Private Sub btnRechercher_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnRechercher.Click
        txtAdresse.Text = Trim(txtAdresse.Text)
        txtNomEntreprise.Text = Trim(txtNomEntreprise.Text)
        txtCodePostal.Text = Trim(txtCodePostal.Text)
        txtTel.Text = Trim(txtTel.Text)
        messageErreur = "Erreur: "
        If validerDonnee() Then

            ' MsgBox(creerRequete)
            remplirListView()
            btnModifier.Enabled = False
            btnSupprimer.Enabled = False

        Else
            MsgBox(messageErreur)

        End If
    End Sub

    Private Sub cboProvince_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cboProvince.SelectedIndexChanged

    End Sub

    Private Sub cboProvince_SelectionChangeCommitted(ByVal sender As Object, ByVal e As System.EventArgs) Handles cboProvince.SelectionChangeCommitted
        remplirListCbo("ville", "Tous", cboVille, " WHERE v.CodeProvince = p.CodeProvince AND p.nomProvince = '" & cboProvince.SelectedItem & "'")
    End Sub

    Private Sub btnRaz_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnRaz.Click
        txtNomEntreprise.Text = Nothing
        txtAdresse.Text = Nothing
        cboProvince.Text = cboProvince.Items(0)
        cboVille.Text = cboVille.Items(0)
        txtTel.Text = Nothing
        txtCodePostal.Text = Nothing

        chkActif.Checked = True
        btnRechercher.PerformClick()
    End Sub

    Private Sub lstResultat_MouseClick(ByVal sender As Object, ByVal e As System.Windows.Forms.MouseEventArgs) Handles lstResultat.MouseClick
        If lstResultat.SelectedItems(0).SubItems(4).Text = "True" Then
            btnSupprimer.Text = "Supprimer"
        Else
            btnSupprimer.Text = "Activer"
        End If

        btnSupprimer.Enabled = True
        btnModifier.Enabled = True
        If saisie = True Then
            btnSelectionner.Enabled = True
        Else
            btnSelectionner.Enabled = False
        End If
    End Sub

    

    Private Sub btnSelectionner_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSelectionner.Click
        idEntreprise = getNoEntreprise(lstResultat.SelectedItems(0).SubItems(0).Text, lstResultat.SelectedItems(0).SubItems(2).Text)
        InscriptionClients.updateEntreprise(idEntreprise)
        saisie = False
        Me.Close()

    End Sub

    Private Sub lstResultat_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles lstResultat.SelectedIndexChanged

    End Sub


    Private Sub btnAnnuler_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAnnuler.Click
        Me.Close()
    End Sub
End Class